﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using Auto.Data.DBUtility;//请先添加引用
using Auto.Data.BusMan.IDAL;
using Auto.Components;
using Model = Auto.Data.BusMan.Model;
namespace Auto.Data.BusMan.SQLServerDAL
{
    /// <summary>
    ///  Data Access Class Drivers。
    /// </summary>
    public class Drivers : IDrivers
    {
        #region --New--
        /// <sumary> 
        /// Constructed Function 
        /// </sumary> 
        public Drivers()
        {
        }
        /// <summary>
        /// Get Model
        /// </summary>
        /// <param name="objKey">key of Table</param>
        public Model.Drivers GetModel(params object[] objKey)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT TOP 1 RecID, DriverID, DriverName,DriverPic, CompanyID, CompanyName, Sex, BirthDay, FamilyAddress, CellPhone, HomePhone, IDCardNO, DrivingLicenseType, DrivingLicenseGetDate, BusIdentificationNO, WorkCompany, WorkType, IDCardNOPic, DrivingLicense, DrivingPermit, OperationCertificate, SpecialCertificate, PoliceStationCertificate, Contract, ContractDate, ContractCycle, SecurityAgreementDate, SecurityAgreementCycle, HealthDeclareForm, HealthDeclareDate, Guarantor, RecDivID, RecAcct, RecDate, UserAcct, LastUpdateDate");//Void, VoidAcct, VoidDate, 
            strSql.Append(" FROM Bus_Drivers");
            strSql.Append(" WHERE DriverID=@DriverID");
            SqlParameter[] parameters = SetParametersValue(objKey);
            Model.Drivers model;
            DataRow dr = DbHelper.GetRecord(strSql.ToString(), parameters);
            if (dr != null)
            {
                model = new Model.Drivers(dr);
                return model;
            }
            else
            {
                return null;
            }
        }
        #endregion --New--
        #region --Operate--
        /// <summary>
        /// Is Exists Or Not
        /// </summary>
        public bool Exists(params object[] objKey)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(1) FROM Bus_Drivers  ");
            strSql.Append(" FROM Bus_Drivers ");
            strSql.Append(" WHERE DriverID=@DriverID");
            SqlParameter[] parameters = SetParametersValue(objKey);
            return DbHelper.Exists(strSql.ToString(), parameters);
        }
        #endregion --Operate--
        #region --SQL--
        /// <summary>
        /// Add One Record
        /// </summary>
        public void Add(Model.Drivers model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO Bus_Drivers(");
            strSql.Append("DriverID,DriverName,DriverPic,CompanyID,CompanyName,Sex,BirthDay,FamilyAddress,CellPhone,HomePhone,IDCardNO,DrivingLicenseType,DrivingLicenseGetDate,BusIdentificationNO,WorkCompany,WorkType,IDCardNOPic,DrivingLicense,DrivingPermit,OperationCertificate,SpecialCertificate,PoliceStationCertificate,Contract,ContractDate,ContractCycle,SecurityAgreementDate,SecurityAgreementCycle,HealthDeclareForm,HealthDeclareDate,Guarantor,RecDivID,RecAcct,RecDate,UserAcct,LastUpdateDate)");//Void,VoidAcct,VoidDate,
            strSql.Append(" VALUES (");
            strSql.Append("@DriverID,@DriverName,@DriverPic,@CompanyID,@CompanyName,@Sex,@BirthDay,@FamilyAddress,@CellPhone,@HomePhone,@IDCardNO,@DrivingLicenseType,@DrivingLicenseGetDate,@BusIdentificationNO,@WorkCompany,@WorkType,@IDCardNOPic,@DrivingLicense,@DrivingPermit,@OperationCertificate,@SpecialCertificate,@PoliceStationCertificate,@Contract,@ContractDate,@ContractCycle,@SecurityAgreementDate,@SecurityAgreementCycle,@HealthDeclareForm,@HealthDeclareDate,@Guarantor,@RecDivID,@RecAcct,@RecDate,@UserAcct,@LastUpdateDate)");//@Void,@VoidAcct,@VoidDate,
            strSql.Append(";UPDATE Bus_Drivers SET DriverID='A'+RIGHT('000'+ CONVERT(NVARCHAR(4),RecID),4) WHERE RecID=@@IDENTITY");
            strSql.Append("; SELECT DriverID FROM Bus_Drivers WHERE RecID=@@IDENTITY");
            SqlParameter[] parameters = SetParametersValue(model);
            IDbCommand cmd = DbHelper.NewCommandWithTransaction();
            try
            {
                object obj = DbHelper.ExecuteScalar(ref cmd, strSql.ToString(), parameters);
                
                if (obj == null)
                {
                    throw new Exception("Error When Insert Data Into Drivers.");
                }

                model.DriverID = obj.ToString();
                DriverBuses driverBuses = new DriverBuses();
                driverBuses.Add(ref cmd, model);


                DbHelper.Commit(ref cmd);
            }
            catch (Exception ex)
            {
                DbHelper.Rollback(ref cmd);
                throw ex;
            }
        }
        /// <summary>
        /// Update One Record
        /// </summary>
        public void Update(Model.Drivers model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE Bus_Drivers SET ");
            strSql.Append("DriverName=@DriverName,");
            strSql.Append("DriverPic=@DriverPic,");
            strSql.Append("CompanyID=@CompanyID,");
            strSql.Append("CompanyName=@CompanyName,");
            strSql.Append("Sex=@Sex,");
            strSql.Append("BirthDay=@BirthDay,");
            strSql.Append("FamilyAddress=@FamilyAddress,");
            strSql.Append("CellPhone=@CellPhone,");
            strSql.Append("HomePhone=@HomePhone,");
            strSql.Append("IDCardNO=@IDCardNO,");
            strSql.Append("DrivingLicenseType=@DrivingLicenseType,");
            strSql.Append("DrivingLicenseGetDate=@DrivingLicenseGetDate,");
            strSql.Append("BusIdentificationNO=@BusIdentificationNO,");
            strSql.Append("WorkCompany=@WorkCompany,");
            strSql.Append("WorkType=@WorkType,");
            strSql.Append("IDCardNOPic=@IDCardNOPic,");
            strSql.Append("DrivingLicense=@DrivingLicense,");
            strSql.Append("DrivingPermit=@DrivingPermit,");
            strSql.Append("OperationCertificate=@OperationCertificate,");
            strSql.Append("SpecialCertificate=@SpecialCertificate,");
            strSql.Append("PoliceStationCertificate=@PoliceStationCertificate,");
            strSql.Append("Contract=@Contract,");
            strSql.Append("ContractDate=@ContractDate,");
            strSql.Append("ContractCycle=@ContractCycle,");
            strSql.Append("SecurityAgreementDate=@SecurityAgreementDate,");
            strSql.Append("SecurityAgreementCycle=@SecurityAgreementCycle,");
            strSql.Append("HealthDeclareForm=@HealthDeclareForm,");
            strSql.Append("HealthDeclareDate=@HealthDeclareDate,");
            strSql.Append("Guarantor=@Guarantor,");
            strSql.Append("RecDivID=@RecDivID,");
            strSql.Append("RecAcct=@RecAcct,");
            strSql.Append("RecDate=@RecDate,");
            //strSql.Append("Void=@Void,");
            //strSql.Append("VoidAcct=@VoidAcct,");
            //strSql.Append("VoidDate=@VoidDate,");
            strSql.Append("UserAcct=@UserAcct,");
            strSql.Append("LastUpdateDate=@LastUpdateDate");
            strSql.Append(" WHERE DriverID=@DriverID ");
            SqlParameter[] parameters = SetParametersValue(model);

            IDbCommand cmd = DbHelper.NewCommandWithTransaction();
            try
            {
                DbHelper.ExecuteNonQuery(ref cmd, strSql.ToString(), parameters);
                DriverBuses driverBuses = new DriverBuses();
                driverBuses.Add(ref cmd, model);
                DbHelper.Commit(ref cmd);
            }
            catch (Exception ex)
            {
                DbHelper.Rollback(ref cmd);
                throw ex;
            }

        }
        /// <summary>
        /// Delete One Record
        /// </summary>
        public void Delete(params object[] objKey)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" DELETE Bus_Drivers ");
            strSql.Append(" WHERE DriverID=@DriverID");
            SqlParameter[] parameters = SetParametersValue(objKey);
            IDbCommand cmd = DbHelper.NewCommandWithTransaction();
            try
            {
                DbHelper.ExecuteNonQuery(ref cmd, strSql.ToString(), parameters);
                DriverBuses driverBuses = new DriverBuses();
                driverBuses.Delete(ref cmd,objKey[0].ToString());
                DbHelper.Commit(ref cmd);
            }
            catch (Exception ex)
            {
                DbHelper.Rollback(ref cmd);
                throw ex;
            }
        }
        #endregion --SQL--
        #region --Get Data--
        /// <sumary> 
        /// Get Data By Primary Key 
        /// </sumary> 
        /// <param name="objKey">Key of Table</param> 
        /// <returns>DataTable</returns> 
        public DataTable GetDataByKeyValue(params object[] objKey)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT RecID, DriverID, DriverName,DriverPic, CompanyID, CompanyName, Sex, BirthDay, FamilyAddress, CellPhone, HomePhone, IDCardNO, DrivingLicenseType, DrivingLicenseGetDate, BusIdentificationNO, WorkCompany, WorkType, IDCardNOPic, DrivingLicense, DrivingPermit, OperationCertificate, SpecialCertificate, PoliceStationCertificate, Contract, ContractDate, ContractCycle, SecurityAgreementDate, SecurityAgreementCycle, HealthDeclareForm, HealthDeclareDate, Guarantor, RecDivID, RecAcct, RecDate,  UserAcct, LastUpdateDate");//Void, VoidAcct, VoidDate,
            strSql.Append(" FROM Bus_Drivers");
            strSql.Append(" WHERE DriverID=@DriverID");
            SqlParameter[] parameters = SetParametersValue(objKey);
            return DbHelper.ExecuteQuery(strSql.ToString(), parameters).Tables[0];
        }
        /// <sumary> 
        /// Get Data List 
        /// </sumary> 
        /// <param name="strWhere">strWhere Format:AND Colname='?' AND ColName2='?'... </param> 
        /// <returns>DataTable</returns> 
        public DataTable GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  RecID, DriverID, DriverName,DriverPic, CompanyID, CompanyName, Sex, BirthDay, FamilyAddress, CellPhone, HomePhone, IDCardNO, DrivingLicenseType, DrivingLicenseGetDate, BusIdentificationNO, WorkCompany, WorkType, IDCardNOPic, DrivingLicense, DrivingPermit, OperationCertificate, SpecialCertificate, PoliceStationCertificate, Contract, ContractDate, ContractCycle, SecurityAgreementDate, SecurityAgreementCycle, HealthDeclareForm, HealthDeclareDate, Guarantor, RecDivID, RecAcct, RecDate, UserAcct, LastUpdateDate");//Void, VoidAcct, VoidDate, 
            strSql.Append(" FROM Bus_Drivers ");
            strSql.Append(" WHERE Void=0 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append( strWhere);
            }
            return DbHelper.ExecuteQuery(strSql.ToString()).Tables[0];
        }
        /// <sumary> 
        /// Get TOP Rows Record 
        /// </sumary> 
        /// <param name="top">TOP Rows</param> 
        /// <param name="strWhere">strWhere Format:AND ColName1='?' AND ColName2='?' ..</param> 
        /// <param name="fieldOrder">Field Order By. Format: ColName1,ColName2...</param> 
        /// <returns>DataTable:TOP Rows</returns> 
        public DataTable GetList(int top, string strWhere, string fieldOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT ");
            if (top > 0)
            {
                strSql.Append(" TOP " + top.ToString());
            }
            strSql.Append(" RecID, DriverID, DriverName,DriverPic, CompanyID, CompanyName, Sex, BirthDay, FamilyAddress, CellPhone, HomePhone, IDCardNO, DrivingLicenseType, DrivingLicenseGetDate, BusIdentificationNO, WorkCompany, WorkType, IDCardNOPic, DrivingLicense, DrivingPermit, OperationCertificate, SpecialCertificate, PoliceStationCertificate, Contract, ContractDate, ContractCycle, SecurityAgreementDate, SecurityAgreementCycle, HealthDeclareForm, HealthDeclareDate, Guarantor, RecDivID, RecAcct, RecDate,UserAcct, LastUpdateDate "); //Void, VoidAcct, VoidDate, 
            strSql.Append(" FROM Bus_Drivers ");
            strSql.Append(" WHERE Void=0");
            if (strWhere.Trim() != "")
            {
                strSql.Append( strWhere);
            }
            strSql.Append(" ORDER BY " + fieldOrder);
            return DbHelper.ExecuteQuery(strSql.ToString()).Tables[0];
        }
        /// <sumary> 
        /// Convert Table to Model List<Model.Drivers> 
        /// </sumary> 
        /// <param name="dt">DataTable Of PMSProjBasic</param> 
        /// <returns>List<Model.Drivers></returns> 
        public List<Model.Drivers> DataTableToList(DataTable dt)
        {
            List<Model.Drivers> modelList = new List<Model.Drivers>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                Model.Drivers model;
                for (int n = 0; n < rowsCount; n++)
                {
                    DataRow dr = dt.Rows[n];
                    model = new Model.Drivers(dr);
                    modelList.Add(model);
                }
            }
            return modelList;
        }
        /// <summary>
        /// Get Data List By Page
        /// </summary>
        public DataTable GetListByPage(int pageSize, int pageIndex, string strWhere)
        {
            SqlParameter[] parameters = {
		   new SqlParameter("@tblName", SqlDbType.VarChar, 255),
		   new SqlParameter("@fldName", SqlDbType.VarChar, 255),
		   new SqlParameter("@PageSize", SqlDbType.Int),
		   new SqlParameter("@PageIndex", SqlDbType.Int),
		   new SqlParameter("@IsReCount", SqlDbType.Bit),
		   new SqlParameter("@OrderType", SqlDbType.Bit),
		   new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
		   };
            parameters[0].Value = "Bus_Drivers";
            parameters[1].Value = "RecID";
            parameters[2].Value = pageSize;
            parameters[3].Value = pageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;
            return DbHelper.ExecuteQueryProcedure("UP_GetRecordByPage", parameters, "ds").Tables[0];
        }

        #region for report 

        /// <summary>
        /// 查询数据给报表
        /// </summary>
        /// <param name="divID">单位编号</param>
        /// <param name="busIDNO">驾驶员姓名</param>
        /// <param name="busSpec">车辆牌号</param>
        /// <param name="keywords">关键字</param>
        /// <returns>返回DT</returns>
        public DataTable GetListWithDesc(string divID, string driverName, string busIDNO, string keywords)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT [DriverID]
                              ,[DriverName]
                              ,[DriverPic]
                              ,[CompanyID]
                              ,[CompanyName]
                              ,[Sex]
                              ,[BirthDay]
                              ,[FamilyAddress]
                              ,[CellPhone]
                              ,[HomePhone]
                              ,[IDCardNO]
                              ,[DrivingLicenseType],spd.PhraseText AS DrivingLicenseTypeDesc
                              ,[DrivingLicenseGetDate]
                              ,[BusIdentificationNO]
                              ,[WorkCompany],d.ShortName AS WorkCompanyName
                              ,[WorkType],spdwt.PhraseText AS WorkTypeDesc
                              ,[IDCardNOPic]
                              ,[DrivingLicense]
                              ,[DrivingPermit]
                              ,[OperationCertificate]
                              ,[SpecialCertificate]
                              ,[PoliceStationCertificate]
                              ,[Contract]
                              ,[ContractDate]
                              ,[ContractCycle]
                              ,[SecurityAgreementDate]
                              ,[SecurityAgreementCycle]
                              ,[HealthDeclareForm]
                              ,[HealthDeclareDate]
                              ,[Guarantor]
                              ,[RecDivID]
                              ,[RecAcct]
                              ,[RecDate]
                          FROM [Bus_Drivers] driver
                          LEFT JOIN _Division d ON d.DivID=driver.WorkCompany
                          LEFT JOIN _SysPhraseDetail spd ON spd.PhraseID='BMDrivingLicenseType' AND spd.PhraseValue=driver.DrivingLicenseType
                          LEFT JOIN _SysPhraseDetail spdwt ON spdwt.PhraseID='BMWorkType' AND spdwt.PhraseValue=driver.WorkType");
            strSql.Append(" WHERE driver.Void=0 ");
            if (divID != "")
            {
                strSql.Append(" AND driver.CompanyID = '" + divID + "'");
            }
            if (busIDNO != "")
            {
                strSql.Append(" AND driver.BusIdentificationNO LIKE N'%" + busIDNO + "%'");
            }
            if (driverName != "")
            {
                strSql.Append(" AND driver.DriverName LIKE N'%" + driverName + "%'");
            }

            return DbHelper.ExecuteQuery(strSql.ToString()).Tables[0];
        }

        #endregion

        #endregion --Get Data--
        #region ----PrivateSet--
        /// <sumary> 
        /// Set Parameters Value 
        /// </sumary> 
        /// <param name="model">Model</param> 
        /// <returns>Parameters</returns> 
        private SqlParameter[] SetParametersValue(Model.Drivers model)
        {
            #region --Init Data--
            if (model.RecDate == DateTime.MinValue)
            {
                model.RecDate = DateTime.Now;
            }
            if (string.IsNullOrEmpty(model.RecAcct))
            {
                model.RecAcct = model.UserAcct;
            }

            model.LastUpdateDate = DateTime.Now;
            #endregion --Init Data--
            SqlParameter[] parameters = { 
                                    		StrHelper.MakeParam("@DriverID",SqlDbType.VarChar,20,ParameterDirection.Input,model.DriverID),
                                    		StrHelper.MakeParam("@DriverName",SqlDbType.NVarChar,20,ParameterDirection.Input,model.DriverName),
                                    		StrHelper.MakeParam("@DriverPic",SqlDbType.NVarChar,100,ParameterDirection.Input,model.DriverPic),
                                            StrHelper.MakeParam("@CompanyID",SqlDbType.VarChar,20,ParameterDirection.Input,model.CompanyID),
                                    		StrHelper.MakeParam("@CompanyName",SqlDbType.NVarChar,50,ParameterDirection.Input,model.CompanyName),
                                    		StrHelper.MakeParam("@Sex",SqlDbType.NChar,1,ParameterDirection.Input,model.Sex),
                                    		StrHelper.MakeParam("@BirthDay",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.BirthDay),
                                    		StrHelper.MakeParam("@FamilyAddress",SqlDbType.NVarChar,50,ParameterDirection.Input,model.FamilyAddress),
                                    		StrHelper.MakeParam("@CellPhone",SqlDbType.VarChar,20,ParameterDirection.Input,model.CellPhone),
                                    		StrHelper.MakeParam("@HomePhone",SqlDbType.VarChar,20,ParameterDirection.Input,model.HomePhone),
                                    		StrHelper.MakeParam("@IDCardNO",SqlDbType.VarChar,20,ParameterDirection.Input,model.IDCardNO),
                                    		StrHelper.MakeParam("@DrivingLicenseType",SqlDbType.VarChar,20,ParameterDirection.Input,model.DrivingLicenseType),
                                    		StrHelper.MakeParam("@DrivingLicenseGetDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.DrivingLicenseGetDate),
                                    		StrHelper.MakeParam("@BusIdentificationNO",SqlDbType.NVarChar,50,ParameterDirection.Input,model.BusIdentificationNO),
                                    		StrHelper.MakeParam("@WorkCompany",SqlDbType.NVarChar,50,ParameterDirection.Input,model.WorkCompany),
                                    		StrHelper.MakeParam("@WorkType",SqlDbType.NVarChar,20,ParameterDirection.Input,model.WorkType),
                                    		StrHelper.MakeParam("@IDCardNOPic",SqlDbType.NVarChar,100,ParameterDirection.Input,model.IDCardNOPic),
                                    		StrHelper.MakeParam("@DrivingLicense",SqlDbType.NVarChar,100,ParameterDirection.Input,model.DrivingLicense),
                                    		StrHelper.MakeParam("@DrivingPermit",SqlDbType.NVarChar,100,ParameterDirection.Input,model.DrivingPermit),
                                    		StrHelper.MakeParam("@OperationCertificate",SqlDbType.NVarChar,100,ParameterDirection.Input,model.OperationCertificate),
                                    		StrHelper.MakeParam("@SpecialCertificate",SqlDbType.NVarChar,100,ParameterDirection.Input,model.SpecialCertificate),
                                    		StrHelper.MakeParam("@PoliceStationCertificate",SqlDbType.NVarChar,100,ParameterDirection.Input,model.PoliceStationCertificate),
                                    		StrHelper.MakeParam("@Contract",SqlDbType.NVarChar,100,ParameterDirection.Input,model.Contract),
                                    		StrHelper.MakeParam("@ContractDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.ContractDate),
                                    		StrHelper.MakeParam("@ContractCycle",SqlDbType.Int,0,ParameterDirection.Input,model.ContractCycle),
                                    		StrHelper.MakeParam("@SecurityAgreementDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.SecurityAgreementDate),
                                    		StrHelper.MakeParam("@SecurityAgreementCycle",SqlDbType.Int,0,ParameterDirection.Input,model.SecurityAgreementCycle),
                                    		StrHelper.MakeParam("@HealthDeclareForm",SqlDbType.NVarChar,100,ParameterDirection.Input,model.HealthDeclareForm),
                                    		StrHelper.MakeParam("@HealthDeclareDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.HealthDeclareDate),
                                    		StrHelper.MakeParam("@Guarantor",SqlDbType.NVarChar,50,ParameterDirection.Input,model.Guarantor),
                                    		StrHelper.MakeParam("@RecDivID",SqlDbType.VarChar,20,ParameterDirection.Input,model.RecDivID),
                                    		StrHelper.MakeParam("@RecAcct",SqlDbType.VarChar,20,ParameterDirection.Input,model.RecAcct),
                                    		StrHelper.MakeParam("@RecDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.RecDate),
                                            //StrHelper.MakeParam("@Void",SqlDbType.Bit,0,ParameterDirection.Input,model.Void),
                                            //StrHelper.MakeParam("@VoidAcct",SqlDbType.VarChar,20,ParameterDirection.Input,model.VoidAcct),
                                            //StrHelper.MakeParam("@VoidDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.VoidDate),
                                    		StrHelper.MakeParam("@UserAcct",SqlDbType.VarChar,20,ParameterDirection.Input,model.UserAcct),
                                    		StrHelper.MakeParam("@LastUpdateDate",SqlDbType.SmallDateTime,0,ParameterDirection.Input,model.LastUpdateDate),

                                 		};
            return parameters;
        }
        /// <sumary> 
        /// Set Parameter Value of Key 
        /// </sumary> 
        /// <param name="objKey">Key Values</param> 
        /// <returns>Parameters</returns> 
        private SqlParameter[] SetParametersValue(object[] objKey)
        {
            SqlParameter[] parameters = { 
                                    		StrHelper.MakeParam("@DriverID",SqlDbType.VarChar,20,ParameterDirection.Input,objKey[0]),

                                 		};
            return parameters;
        }
        #endregion ----PrivateSet--


    }
}

